package com.informet.ipd.modelo.dao;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */



import com.informet.ipd.modelo.vo.CrecerConDeporteVO;
import com.informet.ipd.modelo.vo.TalentoVO;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author LAPTOP01
 */
public class TalentoEvalTecnicaDao extends DBase{


  Connection conn = null;

    public TalentoEvalTecnicaDao() {
        super();
    }


    public void  setListAspectoTecnioCombo(TalentoVO bean, ResultSet rs) throws SQLException{
          bean.setCodAspTecnico(rs.getString("NUM_CODASPTEC_PK"));
          bean.setDescAspTecnico(rs.getString("TX_DESCASPTEC"));          
    }

    public void  setListAspectoTecnioPuntoEntrada(TalentoVO bean, ResultSet rs) throws SQLException{

          bean.setCodAspTecnico(rs.getString("NUM_CODASPTEC_PK"));
          bean.setDescAspTecnico(rs.getString("TX_DESCASPTEC"));

          bean.setCodAlumCcd(rs.getString("NUM_codalumtal_pk"));
          bean.setCodCcd(Integer.parseInt(rs.getString("num_talento_pk")));

          bean.setNumEntradaEt(rs.getString("num_entradaet"));

          bean.setNumPuntuacionPersonal(rs.getString("num_resultadopersonal"));
    }

    public List getListAspectosTecnicosPorDisciplina(String codDisc,String codAlumno,String  numNivelTalento) throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<TalentoVO> lista = new ArrayList<TalentoVO>(0);
        String sql = "";
        
        try {
            conn = DriverManager.getConnection(url, usuario, password);            
            stmt = conn.createStatement();
                        sql = " select "+
                              "          * "+
                              "  from me_asptecnicostal "+
                              "  where num_coddisc_pk="+codDisc+
                              "            and num_codasptec_pk not in (select  "+
                              "                                              num_codasptec_pk  "+
                              "                                      from me_evatectal "+
                              "                                      where num_codalumtal_pk= "+codAlumno+
                              "                               )"+
                              "            and NUM_NIVELTAL="+numNivelTalento
                              ;
            rs = stmt.executeQuery(sql);
            TalentoVO bean1 = null;
            while (rs.next()) {
                bean1 = new TalentoVO();
                setListAspectoTecnioCombo(bean1, rs);
                lista.add(bean1);
            }
           
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
      // JOptionPane.showMessageDialog(null, lista.size());
        return lista;
    }
       

    public List getListAspectosTecnicosPunatajeEntrada(String codDisc,String codAlumno,String  numNivelTalento) throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<TalentoVO> lista = new ArrayList<TalentoVO>(0);
        String sql = "";

        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
                        sql = " select "+
                              "  isnull(num_entradaet,0) as num_entradaet, "+
                              "          * "+
                              " from me_evatectal e inner join me_asptecnicostal a on a.num_codasptec_pk=e.num_codasptec_pk"+
                              " where a.num_coddisc_pk="+codDisc+
                              "         and e.num_codalumtal_pk="+codAlumno+
                              "         and a.num_niveltal="+numNivelTalento
                              ;


            rs = stmt.executeQuery(sql);
            TalentoVO bean1 = null;
            while (rs.next()) {
                bean1 = new TalentoVO();
                setListAspectoTecnioPuntoEntrada(bean1, rs);
                lista.add(bean1);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
      // JOptionPane.showMessageDialog(null, lista.size());
        return lista;
    }

      public String spMan(TalentoVO bean , String putuacionPersonal) {
        String msj = "";
        //String codP=bean.getCodPrograma();
        try {
        	
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_update_TalentoAlumnoEvaTecnica(?,?,?,?,?,?)}");
            //codActividad, codAnio, titu_programa,justificacion,fechaInicio, fechaFin,monto
            cs.setString(1, bean.getCodAspTecnico());
            cs.setString(2, bean.getNumEntradaEt());
            cs.setInt(3, bean.getCodCcd());
            cs.setString(4, bean.getCodAlumno());
            cs.setString(5, bean.getTipoOper());
            cs.setString(6, putuacionPersonal);
            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }

}
